﻿-- =============================================
-- Author:		Jon Stocksdale
-- Create date: April 25 2016
-- Description:	Revision of Taylor's ViewEntityRelationships_Changed to fetch a list of related entities based on a list
--              of PERMITTED relationships - The assumption being that whatever CALLS this Stored Procedure has verified
--              permissions from a RelationshipChain.
-- =============================================
CREATE PROCEDURE [dbo].[_OLD_ViewEntityRelationships_Jon] 
	-- The entity whose permissions need to be returned
	@Entity1ID nvarchar(100),
	@ValidRelationshipList XML,
	@FilterStartDate datetime = null,
	@FilterEndDate datetime = null,
	@FilterTimeDiv nvarchar(100) = null
	-- The date for filtering any relationships that contain a start or end date
	-- Specifying only a Start Date returns all relationships that are valid ON THAT DATE!
	-- Specifying a Start Date AND End Date returns all relationships valid anytime between those two dates.
	-- TimeDiv can be specific TimeDiv ShortName or the word CURRENT.TimeDivType
	-- Example:  Current.SchoolYear or Current.Term
	-- When TimeDiv exists, StartDate and EndDate are ignored and the startdate and enddate of the TimeDiv is used
	-- If ALL filters are NULL, use today for StartDate
	-- Set TimeDiv to ALL to get all relationships ever.
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Get the XML list of valid relationships
	DECLARE @handle INT  
	DECLARE @PrepareXmlStatus INT  
	EXEC @PrepareXmlStatus= sp_xml_preparedocument @handle OUTPUT, @ValidRelationshipList  
	CREATE TABLE #RELATIONSHIPLIST (
		ShortRelationshipName NVarchar(20),
		IsRevRelationship bit
	)
	INSERT INTO #RELATIONSHIPLIST(ShortRelationshipName, IsRevRelationship)
	  SELECT  ShortRelationshipName, IsRevRelationship
		FROM    OPENXML(@handle, '/rows/row', 2)  
		WITH (
			CanPropagate BIT,
			ShortRelationshipName NVARCHAR(20),
			IsRevRelationship BIT
		) AS X 
	EXEC sp_xml_removedocument @handle 

	
	If (@FilterTimeDiv is NOT null) BEGIN
	-- Lookup TimeDiv and set StartDate and EndDate appropriately.
		If SUBSTRING(@FilterTimeDiv,0,8) = 'CURRENT' BEGIN
			SELECT @FilterStartDate=StartDate, @FilterEndDate=EndDate FROM dbo.TimeDivisions WHERE StartDate < SYSDATETIME() AND EndDate > SYSDATETIME() AND TimeDivType = SUBSTRING(@FilterTimeDiv,9,100)
			PRINT 'Hello'
		END
		ELSE BEGIN
			SELECT @FilterStartDate=StartDate, @FilterEndDate=EndDate FROM dbo.TimeDivisions WHERE ShortName = @FilterTimeDiv
		END
	END
	PRINT @FilterStartDate
	
	-- If there was no Time Division specified or found, then the dates would not have been set.
	-- The following code should work either way
		IF (@FilterStartDate is null) BEGIN
		-- If there is no Start Date, assume Today.
			SET @FilterStartDate = SYSDATETIME()
		END
		IF (@FilterEndDate is null) BEGIN
		-- If there is no End Date, then assume a SINGLE date
			SET @FilterEndDate = @FilterStartDate
		END

	

	CREATE TABLE #EntityRelationships (
		Entity1ID nvarchar(100), 
		Entity2ID nvarchar(100), 
		ShortRelationshipName nvarchar(20),
		IsRevRelationship nvarchar(20), 
		StartDate datetime, 
		EndDate datetime, 
		SuperRelationship nvarchar(20),
		Entity1Type nvarchar(100),
		Entity2Type nvarchar(100),
		RelationshipName nvarchar(50),
		Entity2Name nvarchar(255)
	)
	DECLARE @NEXTREL nvarchar(20)
	DECLARE @REV bit
	DECLARE @BuildSQL nvarchar(max)
	DECLARE @FriendlyID nvarchar(255)
	DECLARE @SuperRelationship nvarchar(12), @RafikiNOSQL nvarchar(MAX)
	DECLARE @E1Type nvarchar(100), @E2Type nvarchar(100), @RelName nvarchar(50), @RevName nvarchar(50)
	DECLARE @Local int

	-- Walk through the list of valid Relationships:
	DECLARE RelationshipCursor cursor for
		SELECT * FROM #RELATIONSHIPLIST
	Open RelationshipCursor
	Fetch NEXT FROM RelationshipCursor INTO  @NEXTREL, @REV
	WHILE @@FETCH_STATUS = 0 BEGIN
		SELECT @SuperRelationship=SuperRelationship, @RelName=RelationshipName, @RevName=ReverseRelationship,
				@E1Type = Entity1Type, @E2Type=Entity2Type,
				@RafikiNOSQL = RafikiNOSQL
				FROM DefinedEntityRelationships WHERE (@NEXTREL = ShortRelationshipName)
				Print @NEXTREL
				Print @RafikiNOSQL
		IF (@RafikiNOSQL IS NULL) BEGIN
			PRINT 'Local'
			-- The relationship is local:
			IF (@REV = 0) BEGIN
			-- Not a reverse relationship:
				INSERT INTO #EntityRelationships(Entity1ID, Entity2ID, ShortRelationshipName,IsRevRelationship, StartDate, EndDate, SuperRelationship, Entity1Type,Entity2Type,RelationshipName)
					SELECT CAST(E1ID as nvarchar), CAST(E2ID AS nvarchar), ShortRelationshipName,IsRevRelationship, StartDate, EndDate, SuperRelationship, Entity1Type,Entity2Type,RelationshipName
					FROM 
					(SELECT @Entity1ID AS E1ID, ER.Entity2ID AS E2ID, ER.ShortRelationshipName,0 As IsRevRelationship, ER.StartDate, ER.EndDate, DER.SuperRelationship, 
						Entity1Type,Entity2Type,RelationshipName
						FROM  dbo.EntityRelationships AS ER INNER JOIN
							   dbo.DefinedEntityRelationships AS DER ON DER.ShortRelationshipName = ER.ShortRelationshipName
						WHERE  (ER.Entity1ID = @Entity1ID) AND
							(StartDate IS NULL OR StartDate < @FilterEndDate) AND (EndDate IS NULL OR EndDate > @FilterStartDate) AND
							((DER.ShortRelationshipName = @NEXTREL) OR (DER.SuperRelationship = @NEXTREL))
					) AS X
			END
			ELSE BEGIN
			-- Reverse Relationship
				INSERT INTO #EntityRelationships(Entity1ID, Entity2ID, ShortRelationshipName,IsRevRelationship, StartDate, EndDate, SuperRelationship, Entity1Type,Entity2Type,RelationshipName)
					SELECT CAST(E1ID as nvarchar), CAST(E2ID AS nvarchar), ShortRelationshipName,IsRevRelationship, StartDate, EndDate, SuperRelationship, Entity1Type,Entity2Type,RelationshipName
					FROM 
					(SELECT @Entity1ID AS E1ID, ER.Entity1ID AS E2ID, ER.ShortRelationshipName,1 As IsRevRelationship, ER.StartDate, ER.EndDate, DER.SuperRelationship, 
								Entity2Type AS Entity1Type,Entity1Type AS Entity2Type,ReverseRelationship AS RelationshipName
						FROM  dbo.EntityRelationships AS ER INNER JOIN
									 dbo.DefinedEntityRelationships AS DER ON DER.ShortRelationshipName = ER.ShortRelationshipName
						WHERE (ER.Entity2ID = @Entity1ID) AND (NOT (DER.ReverseRelationship IS NULL))
							AND 
							(StartDate IS NULL OR StartDate < @FilterEndDate) AND (EndDate IS NULL OR EndDate > @FilterStartDate) AND
							((DER.ShortRelationshipName = @NEXTREL) OR (DER.SuperRelationship = @NEXTREL))
					) AS X
			END
		END
		ELSE BEGIN
		-- Relationship is External
			SET @FriendlyID = NULL
			-- Also, try to find a FriendlyID to use to speed up the connection
			SELECT @FriendlyID = AttributeValueString FROM Attributes A 
				INNER JOIN DefinedAttributes DA ON A.AttributeID = DA.AttributeID
				WHERE CAST(EntityID AS NVARCHAR) = @Entity1ID AND DA.AttributeName='FriendlyID'

			IF (@REV = 0) BEGIN
			Print 'Not-Reverse'
				SET @BuildSQL = 'SELECT Entity1ID AS E1ID,Entity2ID AS E2ID, "' + @NEXTREL + '" AS ShortRelationshipName, ' 
						  + '0 AS IsRevRelationship,StartDate,EndDate,' + COALESCE('"' + @SuperRelationship + '"','NULL') + ' AS SuperRelationship, '
						  + @E1Type + '" AS Entity1Type, "' + @E2Type + '" As Entity2Type, "' + @RelName + '" As RelationshipName '
						  + ' FROM [{' + @NEXTREL + '} WHERE Entity1ID = "' + @Entity1ID + '" AND STARTDATE < "'
						  + CAST(@FilterEndDate AS NVARCHAR) + '" AND ENDDATE > "' + CAST(@FilterStartDate AS NVARCHAR) + '" ] '
				Print @BuildSQL		 
				SET @BuildSQL = dbo.ReadDataFromDB_ConvertSQL(@BuildSQL,COALESCE(@FriendlyID,''),'','','')
				
				IF LEFT(@BuildSQL,6) <> 'Error:' BEGIN
					EXECUTE('INSERT INTO #EntityRelationships(Entity1ID, Entity2ID, ShortRelationshipName,IsRevRelationship, StartDate, EndDate, 
							SuperRelationship, Entity1Type,Entity2Type,RelationshipName) ' + @BuildSQL)
				END
			END
			ELSE BEGIN
			Print 'Reverse'
				SET @BuildSQL = 'SELECT Entity2ID AS E1ID,Entity1ID AS E2ID, "' + @NEXTREL + '" AS ShortRelationshipName, ' 
						  + '1 AS IsRevRelationship,StartDate,EndDate,' + COALESCE('"' + @SuperRelationship + '"','NULL') + ' AS SuperRelationship, '
						   + @E2Type + '" AS Entity1Type, "' + @E1Type + '" As Entity2Type, "' + @RevName + '" As RelationshipName '
						  + 'FROM [{' + @NEXTREL + '} WHERE Entity1ID = "' + @Entity1ID + '" AND STARTDATE < "'
						  + CAST(@FilterEndDate AS NVARCHAR) + '" AND ENDDATE > "' + CAST(@FilterStartDate AS NVARCHAR) + '" ]'
				SET @BuildSQL = dbo.ReadDataFromDB_ConvertSQL(@BuildSQL,COALESCE(@FriendlyID,''),'','','')
				IF LEFT(@BuildSQL,6) <> 'Error:' BEGIN
					EXECUTE('INSERT INTO #EntityRelationships(Entity1ID, Entity2ID, ShortRelationshipName,IsRevRelationship, StartDate, EndDate, 
							SuperRelationship, Entity1Type,Entity2Type,RelationshipName) ' + @BuildSQL)
			END
		END
	END

	Fetch NEXT FROM RelationshipCursor INTO  @NEXTREL, @REV
	END
	CLOSE RelationshipCursor
	DEALLOCATE RelationshipCursor

 

	SELECT * FROM #EntityRelationships
END